"""
Case Type   : GSC功能模块
Case Name   : gsc开关打开，备机存在表缓存情况下进行switchover，新主执行ddl，dml功能正常
Create At   : 2023/5/19
Owner       : opentestcase024
Description :
    0、查询参数初始值;
    1、设置极GSC参数;
    2、重启数据库，使参数生效;
    3、创建多个数据库;
    4、执行批量建表删除表函数创建，批量建表;
    5、查询所有库并进行连接;
    6、主机对多个库所有表进行查询;
    7、备机对多个库所有表进行查询;
    8、备机执行switchover;
    9、新主机对多个库所有表进行查询;
    10、新备机对多个库所有表进行查询;
    11、备机执行switchover; expect:执行成功
    12、新主机对多个库所有表进行查询;
    13、新备机对多个库所有表进行查询;
Expect      :
    1、设置极GSC参数; expect:成功
    2、重启数据库，使参数生效; expect:重启成功
    3、创建多个数据库; expect:创建成功
    4、执行批量建表删除表函数创建，批量建表; expect:成功
    5、查询所有库并进行连接; expect:连接成功
    6、主机对多个库所有表进行查询; expect:执行成功
    7、备机对多个库所有表进行查询; expect:执行成功
    8、备机执行switchover; expect:执行成功
    9、新主机对多个库所有表进行查询; expect:执行成功
    10、新备机对多个库所有表进行查询; expect:执行成功
    11、备机执行switchover; expect:执行成功
    12、新主机对多个库所有表进行查询; expect:执行成功
    13、新备机对多个库所有表进行查询; expect:执行成功
History     :
    Modified by peilinqian at 2025/1/21:teardown增加集群主备恢复动作，以防对其他用例产生影响
    modified by peilinqian at 2024-6-17: 用例适配资源池化
"""
import os
import time
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node

pri_sh = CommonSH('PrimaryDbUser')
node_num = pri_sh.get_node_num()


@unittest.skipIf(node_num < 3, '非1主2备环境不执行')
class GscTestCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:start----')
        self.log.info("----this is setup----")
        self.pri_root = Node(node='PrimaryRoot')
        self.sta_1_sh = CommonSH('Standby1DbUser')
        self.com = Common()
        self.constant = Constant()

        self.node_num = self.com.get_node_num(self.pri_root)
        self.table_num = 100
        self.db_num = 5
        self.fun_create_name = 'fun_create_tb_gsc0032'
        self.fun_drop_name = 'fun_drop_tb_gsc0032'
        self.db_ex_name = 'db_gsc0032_'
        self.tb_ex_name = 'tb_gsc0032_'
        self.fun_create_tb = f'''
        drop function if exists public.{self.fun_create_name};
        create or replace function public.{self.fun_create_name} (
        table_num integer)
        returns void
        language 'plpgsql'
        cost 100
        volatile 
        as \$body\$
        declare
        v_idx integer := 0;
        v_strtable varchar :='';
        v_strsql varchar :='';
        begin
          while v_idx < table_num loop
          v_idx = v_idx+1;
          v_strtable = concat('{self.tb_ex_name}', v_idx);
          v_strsql = 'create table '||v_strtable||'(idx integer,log varchar)';
          execute v_strsql;
          end loop;
         end
        \$body\$;'''

        self.fun_drop_tb = f'''
        drop function if exists  public.{self.fun_drop_name};
        create or replace function public.{self.fun_drop_name}(
        table_num_in integer)
        returns void
        language 'plpgsql'
        cost 100
        volatile 
        as \$body\$
        declare
        v_idx integer := 0;
        v_strtable varchar :='';
        v_strsql varchar :='';
        begin
          while v_idx < table_num_in loop
          v_idx = v_idx+1;
          v_strtable = concat('{self.tb_ex_name}', v_idx);
          v_strsql = 'drop table if exists '||v_strtable;
          execute v_strsql;
          end loop;
         end
        \$body\$;
        '''
        self.select_sql = ''
        for j in range(1, int(self.table_num) + 1):
            self.select_sql = self.select_sql + \
                              f'select * from {self.tb_ex_name}{j};'

    def test_main(self):
        step_txt = '----step0:查询参数初始值;----'
        self.log.info(step_txt)
        self.init_para1 = self.com.show_param('enable_global_syscache')
        self.init_para2 = self.com.show_param('global_syscache_threshold')

        step_txt = '----step1:设置极GSC参数; expect:成功----'
        self.log.info(step_txt)
        msg = pri_sh.execute_gsguc('set',
                                   self.constant.GSGUC_SUCCESS_MSG,
                                   f"enable_global_syscache= on")
        self.assertTrue(msg, '执行失败:' + step_txt)
        msg = pri_sh.execute_gsguc('set',
                                   self.constant.GSGUC_SUCCESS_MSG,
                                   'global_syscache_threshold=16MB')
        self.assertTrue(msg, '执行失败:' + step_txt)

        step_txt = '----step2:重启数据库，使参数生效; expect:重启成功----'
        self.log.info(step_txt)
        stop_result = pri_sh.stop_db_cluster()
        start_result = pri_sh.start_db_cluster()
        self.assertTrue(stop_result, '执行失败:' + step_txt)
        self.assertTrue(start_result, '执行失败:' + step_txt)

        step_txt = f'----step3:创建{self.db_num}个数据库; expect:创建成功----'
        self.log.info(step_txt)
        for i in range(self.db_num):
            create_db = f'create database {self.db_ex_name}{i};'
            create_result = pri_sh.execut_db_sql(create_db)
            self.log.info(create_result)
            self.assertIn(self.constant.CREATE_DATABASE_SUCCESS,
                          create_result, '执行失败:' + step_txt)

        step_txt = '----step4:执行批量建表删除表函数创建，批量建表; expect:成功----'
        self.log.info(step_txt)
        for i in range(self.db_num):
            result = pri_sh.execut_db_sql(self.fun_create_tb,
                                          dbname=f'{self.db_ex_name}{i}')
            self.log.info(result)
            self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG,
                          result,
                          '执行失败:' + step_txt)
            result = pri_sh.execut_db_sql(self.fun_drop_tb,
                                          dbname=f'{self.db_ex_name}{i}')
            self.log.info(result)
            self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG,
                          result,
                          '执行失败:' + step_txt)

            self.log.info(f"--{self.db_ex_name}{i}执行批量建表函数--")
            create_tb = f"select public.{self.fun_create_name} " \
                f"({self.table_num});"
            result = pri_sh.execut_db_sql(create_tb,
                                          dbname=f'{self.db_ex_name}{i}')
            self.log.info(result)
            check_tb = f"select count(*) from pg_class where relname " \
                f"like '{self.tb_ex_name}%';"
            result = pri_sh.execut_db_sql(check_tb,
                                          dbname=f'{self.db_ex_name}{i}')
            self.log.info(result)
            self.assertEqual(int(result.splitlines()[-2].strip()),
                             self.table_num, '执行失败:' + step_txt)

        step_txt = '----step5:查询所有库并进行连接; expect:连接成功----'
        self.log.info(step_txt)
        select_db_sql = "select datname from pg_database where datname " \
                        "not like('template%');"
        select_db_result = pri_sh.execut_db_sql(select_db_sql)
        self.log.info(select_db_result)
        db_list = select_db_result.splitlines()[2:-1:1]
        for i in db_list:
            con_result = pri_sh.execut_db_sql(
                'select current_database();',
                dbname=f'{i}')
            self.log.info(con_result)
            self.assertIn(i, con_result, '执行失败:' + step_txt)

        step_txt = f'----step6:主机对{self.db_num}个库所有表进行查询; expect:执行成功----'
        self.log.info(step_txt)
        for i in range(self.db_num):
            result = self.sta_1_sh.execut_db_sql(self.select_sql,
                                                 dbname=f'{self.db_ex_name}{i}')
            self.assertEqual(result.count('0 rows'), self.table_num,
                             '执行失败:' + step_txt)

        step_txt = f'----step7:备机对{self.db_num}个库所有表进行查询; expect:执行成功----'
        self.log.info(step_txt)
        for i in range(self.db_num):
            result = pri_sh.execut_db_sql(self.select_sql,
                                          dbname=f'{self.db_ex_name}{i}')
            self.assertEqual(result.count('0 rows'), self.table_num,
                             '执行失败:' + step_txt)

        step_txt = '----step8:备机执行switchover; expect:执行成功----'
        self.log.info(step_txt)
        success_msg = self.constant.SWITCH_SUCCESS_MSG
        switchover_result = self.sta_1_sh.execute_gsctl('switchover',
                                                        success_msg)
        self.assertTrue(switchover_result, '执行失败:' + step_txt)
        refreshconf_result = self.sta_1_sh.exec_refresh_conf()
        self.assertTrue(refreshconf_result, '执行失败:' + step_txt)

        self.log.info('----查询主备状态----')
        status_result = pri_sh.get_db_cluster_status('status')
        self.assertTrue(status_result, '执行失败:' + step_txt)

        step_txt = f'----step9:新主机对{self.db_num}个库所有表进行查询; expect:执行成功----'
        self.log.info(step_txt)
        for i in range(self.db_num):
            result = self.sta_1_sh.execut_db_sql(self.select_sql,
                                                 dbname=f'{self.db_ex_name}{i}')
            self.assertEqual(result.count('0 rows'), self.table_num,
                             '执行失败:' + step_txt)

        step_txt = f'----step10:新备机对{self.db_num}个库所有表进行查询; expect:执行成功----'
        self.log.info(step_txt)
        for i in range(self.db_num):
            result = pri_sh.execut_db_sql(self.select_sql,
                                          dbname=f'{self.db_ex_name}{i}')
            self.assertEqual(result.count('0 rows'), self.table_num,
                             '执行失败:' + step_txt)

        step_txt = '----step11:备机执行switchover; expect:执行成功----'
        self.log.info(step_txt)
        success_msg = self.constant.SWITCH_SUCCESS_MSG
        switchover_result = pri_sh.execute_gsctl('switchover',
                                                 success_msg)
        self.assertTrue(switchover_result, '执行失败:' + step_txt)
        refreshconf_result = pri_sh.exec_refresh_conf()
        self.assertTrue(refreshconf_result, '执行失败:' + step_txt)

        self.log.info('----查询主备状态----')
        status_result = pri_sh.get_db_cluster_status('status')
        self.assertTrue(status_result, '执行失败:' + step_txt)

        step_txt = f'----step12:新主机对{self.db_num}个库所有表进行查询; expect:执行成功----'
        self.log.info(step_txt)
        for i in range(self.db_num):
            result = pri_sh.execut_db_sql(self.select_sql,
                                          dbname=f'{self.db_ex_name}{i}')
            self.assertEqual(result.count('0 rows'), self.table_num,
                             '执行失败:' + step_txt)

        step_txt = f'----step13:新备机对{self.db_num}个库所有表进行查询; expect:执行成功----'
        self.log.info(step_txt)
        for i in range(self.db_num):
            result = self.sta_1_sh.execut_db_sql(self.select_sql,
                                                 dbname=f'{self.db_ex_name}{i}')
            self.assertEqual(result.count('0 rows'), self.table_num,
                             '执行失败:' + step_txt)

    def tearDown(self):
        step_txt = '----this is teardown----'
        self.log.info(step_txt)
        host_name = self.pri_root.sh('hostname').result()
        if pri_sh.check_whether_need_switch(host_name):
            step_txt = '----主备关系异常，进行恢复----'
            self.log.info(step_txt)
            self.log.info('---进行主备切换---')
            success_msg = self.constant.SWITCHOVER_SUCCESS_MSG
            switchover_result = pri_sh.execute_gsctl('switchover',
                                                     success_msg)
            self.log.info(switchover_result)
            refreshconf_result = pri_sh.exec_refresh_conf()
            self.log.info(refreshconf_result)

        step1_txt = '----删除数据库 expect:删除成功----'
        self.log.info(step1_txt)
        drop_db_msg = ''
        for i in range(self.db_num):
            drop_db = f'drop database {self.db_ex_name}{i}'
            drop_db_msg = drop_db_msg + pri_sh.execut_db_sql(drop_db)

        step2_txt = '----恢复参数为初始值并查询; expect:设置成功----'
        self.log.info(step2_txt)
        msg1 = pri_sh.execute_gsguc('set',
                                    self.constant.GSGUC_SUCCESS_MSG,
                                    f"enable_global_syscache="
                                    f"{self.init_para1}")
        msg2 = pri_sh.execute_gsguc('set',
                                    self.constant.GSGUC_SUCCESS_MSG,
                                    f"global_syscache_threshold="
                                    f"{self.init_para2}")
        step3_txt = '----重启数据库，使参数生效; expect:重启成功----'
        self.log.info(step3_txt)
        restart_result = pri_sh.restart_db_cluster()
        step4_txt = '----查询数据库状态; expect:状态正常----'
        self.log.info(step4_txt)
        status = pri_sh.exec_cm_ctl(mode='query', param='-Cv')
        self.log.info(status)
        p_status = status.splitlines()[-1].split('|')[0].split('6001')[-1]
        if 'Primary' in p_status and "cluster_state   : Normal" in status:
            self.log.info('---集群状态正常，无需恢复为初始状态---')
        else:
            recovery = pri_sh.exec_cm_ctl(mode='switchover',
                                          param='-a')
            self.log.info(recovery)
            time.sleep(10)
        status_result = pri_sh.get_db_cluster_status('status')

        flag = drop_db_msg.count(self.constant.DROP_DATABASE_SUCCESS)
        self.assertEqual(flag, self.db_num, '执行失败:' + step_txt + step1_txt)
        self.assertTrue(msg1, '执行失败:' + step_txt + step2_txt)
        self.assertTrue(msg2, '执行失败:' + step_txt + step2_txt)
        self.assertTrue(restart_result, '执行失败:' + step_txt + step3_txt)
        self.assertTrue(status_result, '执行失败:' + step_txt + step4_txt)
        self.log.info(f'----{os.path.basename(__file__)}:end----')
